﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace CRM.Common
{
    public class NPOIHelper
    {

        #region 将DataTable导入到Stream流中
        /// <summary>
        ///  将DataTable导入到Stream流中
        /// </summary>
        /// <param name="SourceTable"></param>
        /// <returns></returns>
        public Stream RenderDataTableToStream(DataTable SourceTable)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            MemoryStream ms = new MemoryStream();

            var sheet = workbook.CreateSheet();

            var headerRow = sheet.CreateRow(0);

            //根据DataTable里面的列来设置Excel里面的列
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {

                var dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;

            }

            workbook.Write(ms);

            ms.Flush();

            ms.Position = 0;

            sheet = null;

            headerRow = null;

            workbook = null;

            return ms;

        }
        #endregion

        #region   将DataTable导入到OutputStream，专门提供给Web下载
        /// <summary>
        ///  将DataTable导入到OutputStream，专门提供给Web下载
        /// </summary>
        /// <param name="SourceTable"></param>
        /// <returns></returns>
        public void RenderDataTableToStream(DataTable SourceTable, Stream stream)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            ISheet sheet = null;

            IRow headerRow = null;

            var sheetrowCount = 65535;

            var rowsTotal = SourceTable.Rows.Count;

            //如果没有数据，则默认为1条数据
            if (rowsTotal == 0)
                rowsTotal = 1;

            var pageCount = Math.Ceiling(Convert.ToDecimal((rowsTotal
                                          / Convert.ToSingle(sheetrowCount))));

            for (int i = 1; i <= pageCount; i++)
            {
                sheet = workbook.CreateSheet("第" + i + "页");

                headerRow = sheet.CreateRow(0);

                //根据DataTable里面的列来设置Excel里面的列
                foreach (DataColumn column in SourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

                var rowIndex = (i - 1) * sheetrowCount;

                var pageRowCount = i * sheetrowCount;

                var index = 1;

                for (int j = rowIndex; j < pageRowCount; j++)
                {
                    //判断一下是否结束
                    if (j >= SourceTable.Rows.Count)
                        break;

                    var dataRow = sheet.CreateRow(index);

                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[j][column].ToString());
                    }

                    index++;
                }
            }

            workbook.Write(stream);

            sheet = null;

            headerRow = null;

            workbook = null;
        }
        #endregion

        #region 将DataTable导出到Excel中
        /// <summary>
        /// 将DataTable导出到Excel中
        /// </summary>
        /// <param name="SourceTable"></param>
        /// <param name="FileName"></param>
        public void RenderDataTableToExcel(DataTable SourceTable, string FileName)
        {

            MemoryStream ms = RenderDataTableToStream(SourceTable) as MemoryStream;

            FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);

            byte[] data = ms.ToArray();

            fs.Write(data, 0, data.Length);

            fs.Flush();

            fs.Close();

            data = null;

            ms = null;

            fs = null;

        }
        #endregion

        #region 将Excel里面的数据导入到Excel
        /// <summary>
        /// 将Excel里面的数据导入到Excel
        /// </summary>
        /// <param name="ExcelFileStream"></param>
        /// <param name="SheetName"></param>
        /// <param name="HeaderRowIndex"></param>
        /// <returns></returns>
        public DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {

            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);

            var sheet = workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            var headerRow = sheet.GetRow(HeaderRowIndex);

            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {

                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

                table.Columns.Add(column);

            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {

                var row = sheet.GetRow(i);

                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)

                    dataRow[j] = row.GetCell(j).ToString();

            }

            ExcelFileStream.Close();

            workbook = null;

            sheet = null;

            return table;

        }

        #endregion

        #region 直接将指定path的Excel导入到DataTable中
        /// <summary>
        /// 直接将指定path的Excel导入到DataTable中
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataTable RenderDataTableFromExcel(string path)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            var sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            var headerRow = sheet.GetRow(0);

            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                var cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);

            }
            return dt;
        }
        #endregion

    }
}
